-- 社区球场预约系统数据库表结构
-- 请在 Supabase SQL 编辑器中执行此脚本

-- 启用必要的扩展
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- 用户表（扩展 Supabase auth.users）
CREATE TABLE public.users (
  id UUID REFERENCES auth.users(id) ON DELETE CASCADE PRIMARY KEY,
  username VARCHAR(50) UNIQUE NOT NULL,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) NOT NULL,
  phone VARCHAR(20),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 球场表
CREATE TABLE public.courts (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  type VARCHAR(50) NOT NULL CHECK (type IN ('basketball', 'badminton', 'tennis')),
  description TEXT,
  image_url VARCHAR(255),
  status VARCHAR(20) DEFAULT 'available' CHECK (status IN ('available', 'maintenance')),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 时间段表
CREATE TABLE public.time_slots (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  court_id UUID REFERENCES public.courts(id) ON DELETE CASCADE NOT NULL,
  time_range VARCHAR(20) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 预约表
CREATE TABLE public.bookings (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  user_id UUID REFERENCES public.users(id) ON DELETE CASCADE NOT NULL,
  court_id UUID REFERENCES public.courts(id) ON DELETE CASCADE NOT NULL,
  time_slot_id UUID REFERENCES public.time_slots(id) ON DELETE CASCADE NOT NULL,
  booking_date DATE NOT NULL,
  status VARCHAR(20) DEFAULT 'confirmed' CHECK (status IN ('confirmed', 'cancelled', 'completed')),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 创建索引以提高查询性能
CREATE INDEX idx_courts_type ON public.courts(type);
CREATE INDEX idx_courts_status ON public.courts(status);
CREATE INDEX idx_time_slots_court_id ON public.time_slots(court_id);
CREATE INDEX idx_bookings_user_id ON public.bookings(user_id);
CREATE INDEX idx_bookings_court_id ON public.bookings(court_id);
CREATE INDEX idx_bookings_date ON public.bookings(booking_date);
CREATE INDEX idx_bookings_status ON public.bookings(status);

-- 行级安全策略 (RLS)
ALTER TABLE public.users ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.courts ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.time_slots ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.bookings ENABLE ROW LEVEL SECURITY;

-- 用户表策略
CREATE POLICY "Users can view own profile" ON public.users
  FOR SELECT USING (auth.uid() = id);

CREATE POLICY "Users can update own profile" ON public.users
  FOR UPDATE USING (auth.uid() = id);

CREATE POLICY "Users can insert own profile" ON public.users
  FOR INSERT WITH CHECK (auth.uid() = id);

-- 球场表策略（所有用户都可以查看）
CREATE POLICY "Anyone can view courts" ON public.courts
  FOR SELECT USING (true);

-- 时间段表策略（所有用户都可以查看）
CREATE POLICY "Anyone can view time slots" ON public.time_slots
  FOR SELECT USING (true);

-- 预约表策略
CREATE POLICY "Users can view own bookings" ON public.bookings
  FOR SELECT USING (auth.uid() = user_id);

CREATE POLICY "Users can insert own bookings" ON public.bookings
  FOR INSERT WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can update own bookings" ON public.bookings
  FOR UPDATE USING (auth.uid() = user_id);

CREATE POLICY "Users can delete own bookings" ON public.bookings
  FOR DELETE USING (auth.uid() = user_id);

-- 创建更新时间触发器函数
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

-- 为相关表添加更新时间触发器
CREATE TRIGGER update_users_updated_at BEFORE UPDATE ON public.users
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_courts_updated_at BEFORE UPDATE ON public.courts
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_bookings_updated_at BEFORE UPDATE ON public.bookings
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- 插入示例数据
INSERT INTO public.courts (name, type, description, image_url) VALUES
('篮球场A', 'basketball', '标准篮球场，配备专业篮球架和灯光设施', 'https://via.placeholder.com/300x200/4A90E2/FFFFFF?text=篮球场A'),
('篮球场B', 'basketball', '标准篮球场，配备专业篮球架和灯光设施', 'https://via.placeholder.com/300x200/4A90E2/FFFFFF?text=篮球场B'),
('羽毛球场A', 'badminton', '专业羽毛球场，配备专业羽毛球网和空调设施', 'https://via.placeholder.com/300x200/7ED321/FFFFFF?text=羽毛球场A'),
('羽毛球场B', 'badminton', '专业羽毛球场，配备专业羽毛球网和空调设施', 'https://via.placeholder.com/300x200/7ED321/FFFFFF?text=羽毛球场B'),
('网球场', 'tennis', '标准网球场，配备专业网球网和灯光设施', 'https://via.placeholder.com/300x200/F5A623/FFFFFF?text=网球场');

-- 插入时间段数据
INSERT INTO public.time_slots (court_id, time_range, price)
SELECT 
  c.id,
  ts.time_range,
  ts.price
FROM public.courts c
CROSS JOIN (
  VALUES 
    ('08:00-10:00', 50.00),
    ('10:00-12:00', 50.00),
    ('14:00-16:00', 60.00),
    ('16:00-18:00', 60.00),
    ('18:00-20:00', 80.00)
) AS ts(time_range, price)
WHERE c.type = 'basketball';

INSERT INTO public.time_slots (court_id, time_range, price)
SELECT 
  c.id,
  ts.time_range,
  ts.price
FROM public.courts c
CROSS JOIN (
  VALUES 
    ('09:00-11:00', 40.00),
    ('11:00-13:00', 40.00),
    ('15:00-17:00', 50.00),
    ('17:00-19:00', 50.00),
    ('19:00-21:00', 60.00)
) AS ts(time_range, price)
WHERE c.type = 'badminton';

INSERT INTO public.time_slots (court_id, time_range, price)
SELECT 
  c.id,
  ts.time_range,
  ts.price
FROM public.courts c
CROSS JOIN (
  VALUES 
    ('08:00-10:00', 80.00),
    ('10:00-12:00', 80.00),
    ('14:00-16:00', 100.00),
    ('16:00-18:00', 100.00),
    ('18:00-20:00', 120.00)
) AS ts(time_range, price)
WHERE c.type = 'tennis';
